<?php
/**
 * Created by PhpStorm.
 * User: wangjun
 * Date: 2016-10-26
 * Time: 11:31
 */

namespace Home\Controller;

class UploadController extends AdminsController{
    public function upload(){
        $unit      = D('unit');
        $personnel = D('personnel');
        $schedule  = D('schedule');
        $connect   = D('connect');
        if(!IS_POST) {
            $this->display();
        }else{
            //dump($_FILES);exit;
            set_time_limit(0);
            //上传excel文件保存在upload文件夹
            if (! empty ( $_FILES ['file_stu'] ['name'] )){
                $tmp_file = $_FILES ['file_stu'] ['tmp_name'];
                $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
                $file_type = $file_types [count ( $file_types ) - 1];
                /*判别是不是.xls文件，判别是不是excel文件*/
                if ((strtolower($file_type) != "xlsx") && (strtolower($file_type) != "xls")) {
                    $this->error( '不是Excel文件，重新上传' );
                }
                /*设置上传路径*/
                $savePath = C('EXCELSAVEPATH');
                if(!is_dir($savePath)){
                    mkdir($savePath,0777,true);
                }
                /*以时间来命名上传的文件*/
                $str = date ( 'Ymdhis' );
                $file_name = $str . "." . $file_type;
                $newfileexcelname = $savePath.'/'.$file_name;
                //判断文件保存成功就调用phpexcel类解析刚刚保存的excel文件
                if(!move_uploaded_file( $tmp_file,$newfileexcelname)){
                    $this->error('上传失败');
                }else{
                    $data = format_excel2array($newfileexcelname,$sheet=0);
                    //echo '<pre>';
                    //dump($data);exit;
                    //判断上传的excel文件表的类型
                    $tablename = $data[1]['A'];
                    if($tablename == '单位信息表'){
                        $count = count($data[2]);
                        if($count != 5){
                            $this->error('单位信息表字段数量不对');
                        }else {
                            if (($data[2]['A'] != '序号') || ($data[2]['B'] != '委托单位') || ($data[2]['C'] != '单位编码') || ($data[2]['D'] != '单位全称') || ($data[2]['E'] != '备注') ) {
                                $this->error('单位信息表字段名不符合规则');
                            }else{
                                foreach($data as $k=>$v){
                                    if($k >= 3){
                                        $arr = [
                                            'id'        => (int)trim($v['A']),
                                            'requester' => (string)trim($v['B']),
                                            'unitsum'   => (string)trim($v['C']),
                                            'dwname'    => (string)trim($v['D']),
                                            'remark'    => (string)trim($v['E']),
                                        ];
                                        $where['requester'] = $arr['requester'];
                                        if($unit->where($where)->find()) {
                                            if( $unit->where($where)->save($arr) === false){
                                               $this->error('数据更新失败');
                                            }
                                        }else{
                                            if(!$unit -> add($arr)){
                                                $this->error('数据插入失败');
                                            }
                                        }
                                    }
                                }
                                $this->success('入库成功',U('upload'),3);
                            }
                        }
                    }else if($tablename == '人员信息表'){
                        $count = count($data[2]);
                        if($count != 6){
                            $this->error('人员信息表字段数量不对');
                        }else {
                            if (($data[2]['A'] != '序号') || ($data[2]['B'] != '单位编码') || ($data[2]['C'] != '委托单位') || ($data[2]['D'] != '委托人') || ($data[2]['E'] != '手机号') || ($data[2]['F'] != '备注')) {
                                $this->error('人员信息表字段名不符合规则');
                            }else{
                                foreach($data as $k=>$v){
                                    if($k >= 3){
                                        $arr =[
                                            'id'        => (int)trim($v['A']),
                                            'unitsum'   => (string)trim($v['B']),
                                            'requester' => (string)trim($v['C']),
                                            'client'    => (string)trim($v['D']),
                                            'tel'       => (string)trim($v['E']),
                                            'remark'    => (string)trim($v['F'])
                                        ];
                                        $where['client'] = $arr['client'];
                                        $where['requester'] = $arr['requester'];
                                        if($personnel->where($where)->find()) {
                                            if($personnel->where($where)->save($arr) === false){
                                                $this->error('数据更新失败');
                                            }
                                        }else{
                                            if(!$personnel->add($arr)){
                                                $this->error('数据插入失败');
                                            }
                                        }
                                    }
                                }
                                $this->success('入库成功',U('upload'),3);
                            }
                        }
                    }else if($tablename == '进度情况表'){
                        $count = count($data[2]);
                        if($count != 19){
                            $this->error('进度情况表字段数量不对');
                        }else {
                            if (($data[2]['A'] != '序号') || ($data[2]['B'] != '证书编号') || ($data[2]['C'] != '计量状态') || ($data[2]['D'] != '任务单号') || ($data[2]['E'] != '型号规格') ||
                                ($data[2]['F'] != '统一编号') || ($data[2]['G'] != '出厂编号') || ($data[2]['H'] != '检定校准单位') || ($data[2]['I'] != '生产厂家') || ($data[2]['J'] != '委托单位') ||
                                ($data[2]['K'] != '委托人') || ($data[2]['L'] != '委托电话') || ($data[2]['M'] != '委托日期') || ($data[2]['N'] != '计量日期') || ($data[2]['O'] != '导入日期')
                                || ($data[2]['P'] != '委托单位编码') || ($data[2]['Q'] != '计量结果') || ($data[2]['R'] != '计量员') || ($data[2]['S'] != '有效期至'))
                            {
                                $this->error('进度情况表字段名不符合规则');
                            }else{
                                foreach($data as $k=>$v){
                                    if($k >= 3){
                                        $arr =[
                                            'id'        => (int)$v['A'],
                                            'dpasum'    => (string)trim($v['B']),
                                            'status'    => (string)trim($v['C']),
                                            'gxsum'     => (string)trim($v['D']),
                                            'xhtype'    => (string)trim($v['E']),
                                            'norm'      => (string)trim($v['F']),
                                            'batch'     => (string)trim($v['G']),
                                            'zlgrage'   => (string)trim($v['H']),
                                            'output'    => (string)trim($v['I']),
                                            'requester' => (string)trim($v['J']),
                                            'client'    => (string)trim($v['K']),
                                            'tel'       => (string)trim($v['L']),
                                            'wtsj'      => (int)strtotime($v['M']),
                                            'wcsj'      => (int)strtotime($v['N']),
                                            'lmportsj'  => time(),
                                            'unitsum'   => (string)trim($v['P']),
                                            'end'       => (string)trim($v['Q']),
                                            'assess'    => (string)trim($v['R']),
                                            'remark'    => (string)trim($v['S']),
                                        ];
                                        $where['dpasum'] = $arr['dpasum'];
                                        if($schedule->where($where)->find()) {
                                            if(false === $schedule->where($where)->save($arr)){
                                                $this->error('数据更新失败,请检查后再操作');
                                            }else{
                                                echo 'upload ok'.'<br>';
                                            }
                                        }else{
                                            if(!$schedule->add($arr)){
                                                $this->error('数据插入失败,请检查后再操作');
                                            }else{
                                                echo 'insert ok'.'<br>';
                                            }
                                        }
                                    }
                                }
                                $this->success('数据上传成功',U('ts_dpa'),1);//推送完成dpa报告
                            }
                        }
                    }elseif($tablename == '交接表'){
                        if(count($data[2]) != 7){
                            $this->error('交接表的字段数量不对');
                        }elseif(($data[2]['A'] != '序号' )||($data[2]['B'] != '证书编号' )||($data[2]['C'] != '领取人' )||($data[2]['D'] != '领取人单位' )||
                                ($data[2]['E'] != '领取人电话' )||($data[2]['F'] != '领取时间' )||($data[2]['G'] != '确认人' ))
                        {
                            $this->error('交接表的字段名称不符合规则,请检查后再操作');
                        }else{
                            foreach($data as $k=>$v){
                                if($k >= 3){
                                    $arr = array(
                                        'dpasum'        => (string)trim($v['B']),
                                        'receivename'   => (string)trim($v['C']),
                                        'requester'     => (string)trim($v['D']),
                                        'tel'           => (string)trim($v['E']),
                                        'gettime'       => (int)strtotime(($v['F'])),
                                        'verifyname'    => (string)trim($v['G'])
                                    );
                                    $where['dpasum'] = $arr['dpasum'];
                                    if($connect->where($where)->find()){
                                        if(false === $connect->where($where)->save($arr)){
                                            $this->error('数据更新失败');
                                        }else{
                                            echo 'upload ok'.'<br>';
                                        }
                                    }else{
                                        if(!$connect->add($arr)){
                                            $this->error('数据添加失败');
                                        }else{
                                            echo 'insert ok'.'<br>';
                                        }
                                    }
                                }
                            }
                            $this->success('数据入库成功',U('upload'),3);
                        }
                    }else{
                        $this->error('上传的表名不符合规则');
                    }
                }
            }
        }
    }
    public function ts_dpa(){
        $personnel = D('personnel');
        $schedule  = D('schedule');
        $user = D('user');
        //查出已完成并且没有被推送过的报告
        if($info = $schedule->field('dpasum,requester')->where("status='已完成' and ts_status=0 ")->select()){
            //把每个单位的dpa拼接成字符串付给每个单位
           // dump($info);
            foreach($info as $k=>&$v){
                $lis[$v['requester']][] = $v['dpasum'];
            }
            //dump($lis);exit;
            foreach($lis as $k=>$v){
                $wcdpa[$k] = implode(',',$v);
            }
           // dump($wcdpa);//每个单位的完成dpa
            $strs = '';
            $wcdpa_str = '';
            foreach ($wcdpa as $k=>$v) {
                $strs .= $k.'('.$v.'),';//把所有的完成dpa号和自己的单位拼接一个大字符串
                $wcdpa_str .= $v.',';//把所有完成的dpa拼接成字符串
            }
            //dump(rtrim($strs,','));
            $wcdpa_str = rtrim($wcdpa_str,',');
            echo $wcdpa_str;
            $res = array_keys($lis); //取出已完成报告的单位
            //dump($res);
            $time = time() - 3600*48;//获取48小时前的时间
            for($i=0;$i<count($res);$i++){
                $list =$personnel->field('tel')->where("requester='{$res[$i]}'")->select();
                foreach($list as $k=>$v){
                    if($bbb = $user->field('openid')->where("tel='{$v['tel']}' and hdtime>'{$time}'")->find()){
                        $aaa[$res[$i]][] = $bbb;
                    }
                }
            }
           //dump($aaa);
            $openidstr = '';
            if($aaa){
                foreach($aaa as $k=>$v){
                    $open[$k] = array_column($v,'openid');
                }
                foreach($open as $k=>$v){
                    $openidstr .= implode(',',$v).',';
                }
            }
           // dump($openidstr);
            $access_token = access_token();
            $url = "https://api.weixin.qq.com/cgi-bin/message/custom/send?access_token={$access_token}";
            foreach($open as $k=>$v ){
                for($i=0;$i<count($v);$i++){
                    foreach($wcdpa as $wk=>$wv){
                        if($wk == $k){
                            $content = '您的委托('.$wcdpa[$wk].')已完成，如须了解更多信息，请进入自助查询！';
                            //使用url编码
                            $content = urlencode($content);
                            $content_arr = array('content' => "{$content}");
                            $reply_arr = array('touser' => "{$v[$i]}", 'msgtype' => 'text', 'text' => $content_arr);
                            //编码成json格式
                            $post = json_encode($reply_arr);
                            //url解码
                            $post = urldecode($post);
                            //dump($post);exit;
                            $info = https_request($url,$post);
                            dump($info);
                            //echo $v[$i].'--'.$wcdpa[$wk].'<br>';
                        }
                    }
                }
            }
            //取出单位为admin的人员的tel和openid
            $admin = $personnel->field('tel')->where("requester='admin'")->select();
            $admin = array_column($admin,'tel');
            for($i=0;$i<count($admin);$i++){
               if($openidinfo = $user->field('openid')->where("tel='$admin[$i]' and hdtime>$time")->find()){
                   $openid_admin[] = $openidinfo;
               }
            }
            if($openid_admin){
                $_openid_admin = implode(',',array_column($openid_admin,'openid'));
                foreach($openid_admin as $k=>$v){
                    $content = '以下单位的委托已完成  '.$strs.'请查阅 !';
                    $content = urlencode($content);
                    $content_arr = array('content' => $content);
                    $reply_arr = array('touser' => "{$v['openid']}", 'msgtype' => 'text', 'text' => $content_arr);
                    $post = json_encode($reply_arr);
                    $post= urldecode($post);
                    $info_admin = https_request($url,$post);
                    dump($info_admin);
                }
            }
            //取出互动48小时内的人员
            if(!empty($openidstr) && isset($_openid_admin)){
                $tsokopenid = $openidstr .$_openid_admin;
            }elseif(!empty($openidstr)){
                $tsokopenid = rtrim($openidstr,',');
            }elseif(isset($_openid_admin)){
                $tsokopenid = $_openid_admin;
            }
            if(!$tsokopenid){
                $this->success('没有成员可推送数据报告','upload',3);
            }else{
                $rest = $user->alias('a')->field('b.client,b.requester')
                    ->join('left join personnel b on a.tel=b.tel')
                    ->where(array(
                        'a.openid' => array('in',$tsokopenid)
                    ))->select();
                $ts_ok_bady = '';
                foreach($rest as $k=>$v){
                    $ts_ok_bady .= $v['requester'] .'--'. $v['client'].'<br>';
                }
                //把已推送的dpa的推送状态改为1
                $where['dpasum'] = array('in',$wcdpa_str);
                $data['ts_status'] = 1;
                if(!$schedule->where($where)->save($data)){
                    $this->success('报告推送状态更改失败,请查证后再试','upload',3);
                }else{
                    if(empty($ts_ok_bady)){
                        echo '没有成员可推送';
                    }else{
                        echo '推送成功,推送成功人员如下 : '.'<br>';
                        echo $ts_ok_bady;
                    }
                }
            }
        }else{
            $this->success('没有可推送的数据报告','upload',3);
        }
    }
}
